RiskCubeDetailV6

V8 Message Definiton

RiskCubeDetail records are published by CoreRiskServers and are designed to be aggregated and analyzed over time. RiskCubeDetail records are published to the SpiderRock Archive Host data pipeline at the end of each trading period.

METADATA

Attribute	Value
Topic	4740-risk-v5
MLink Token	ClientRisk
Product	SRRisk
accessType	SELECT
MLink Endpoint	MLink-Order

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI	''	SR Accnt
secKey_at	enum - AssetType	PRI	'None'
secKey_ts	enum - TickerSrc	PRI	'None'
secKey_tk	VARCHAR(12)	PRI	''
secKey_yr	SMALLINT UNSIGNED	PRI	0
secKey_mn	TINYINT UNSIGNED	PRI	0
secKey_dy	TINYINT UNSIGNED	PRI	0
secKey_xx	DOUBLE	PRI	0
secKey_cp	enum - CallPut	PRI	'Call'
secType	enum - SecType	PRI	'None'	Call Put Stock Future Cash
tradeDate	DATE	PRI	'1900-01-01'
riskView	enum - RiskView	PRI	'Live'
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm
version	enum - RiskCubeVersion		'None'
clearingFirm	VARCHAR(8)		''	Clearing Firm
clearingAccnt	VARCHAR(16)		''	Clearing Firm Accnt
riskServerCode	VARCHAR(6)		''	SR RiskServer server that published this record
viewGroup1	VARCHAR(12)		''	Client defined view group 1 can be fiteredaggregated
viewGroup2	VARCHAR(12)		''	Client defined view group 2 can be fiteredaggregated
viewGroup3	VARCHAR(12)		''	Client defined view group 3 can be fiteredaggregated
ticker_at	enum - AssetType		'None'	DEPRECATED the V2 value the line for a future the head V2 filed it under plus its own code CFEs shared heads VXMVXMT VIXMIN VXT VX VN VXN VXEM VXEEM VXEW VXEWZ GV GVZ OV OVX read product complexKey and familyHead
ticker_ts	enum - TickerSrc		'None'	DEPRECATED the V2 value the line for a future the head V2 filed it under plus its own code CFEs shared heads VXMVXMT VIXMIN VXT VX VN VXN VXEM VXEEM VXEW VXEWZ GV GVZ OV OVX read product complexKey and familyHead
ticker_tk	VARCHAR(12)		''	DEPRECATED the V2 value the line for a future the head V2 filed it under plus its own code CFEs shared heads VXMVXMT VIXMIN VXT VX VN VXN VXEM VXEEM VXEW VXEWZ GV GVZ OV OVX read product complexKey and familyHead
product_at	enum - AssetType		'None'	the futures product CCodeDefinitionV3 9115 key empty for a share or an equity option
product_ts	enum - TickerSrc		'None'	the futures product CCodeDefinitionV3 9115 key empty for a share or an equity option
product_tk	VARCHAR(12)		''	the futures product CCodeDefinitionV3 9115 key empty for a share or an equity option
familyHead_at	enum - AssetType		'None'	the futures family the product is counted in 9115 familyHead empty for a share an equity option or a product that is its own family
familyHead_ts	enum - TickerSrc		'None'	the futures family the product is counted in 9115 familyHead empty for a share an equity option or a product that is its own family
familyHead_tk	VARCHAR(12)		''	the futures family the product is counted in 9115 familyHead empty for a share an equity option or a product that is its own family
complexKey_at	enum - AssetType		'None'	the complex this row rolls up to the position records complexKey empty reads as ticker
complexKey_ts	enum - TickerSrc		'None'	the complex this row rolls up to the position records complexKey empty reads as ticker
complexKey_tk	VARCHAR(12)		''	the complex this row rolls up to the position records complexKey empty reads as ticker
riskClass	VARCHAR(8)		''	Symbol risk class from SymbolControl record
symbolType	enum - SymbolType		'None'	Symbol type
optRiskClass	enum - OptRiskClass		'None'
beta	FLOAT		0	beta usually beta to SPX see AccountConfigbetaSource
betaSource	enum - BetaSource		'None'
sector	VARCHAR(16)		''	Client defined sector from SymbolControl record
indNum	INT		0	Ind Num 00
subNum	INT		0	Sub Num 0000
grpNum	INT		0	Grp Num 000000
nbrNum	INT		0	Nbr Num 00000000
eDays	SMALLINT		0	days to next earnings 5 30
dDays	SMALLINT		0	days to next dividend 5 30
xDelta	FLOAT		0	calls 05 delta puts 05 delta
expCode	enum - ExpCode		'None'	Timetoexpiry code W1 W2 M1 M2 M34 M56 M7C Y1 Y2
skewCode	enum - SkewCode		'None'	Skew bucket code DdDeep DownSide Dn DownSide At ATM Up UpSide DU FarUpSide
eDaysCode	enum - EDaysCode		'None'	Days to next earnings event if any
iVolRange	enum - iVolRange		'None'	Implied ATM Vol Range
liqRange	enum - LiqRange		'None'	Underlier liquidity range publisherclassified 5 levels
atmVolQuantile	FLOAT		0	AtmVol level quantile
atmVolMoveQuantile	FLOAT		0	logatmVolatmVolEMA90d quantile earningscolored move
atmCenMoveQuantile	FLOAT		0	logatmCenatmCenEMA90d quantile earningscensored move
openVegaQuantile	FLOAT		0	market openvega activity quantile
skewDD	FLOAT		0	dd deep downside skew metric xde 045
skewDN	FLOAT		0	dn downside skew metric 045 xde 015
skewAT	FLOAT		0	at atm skew risk metric 015 xde 015
skewUP	FLOAT		0	up upside skew risk metric 015 xde 045
skewDU	FLOAT		0	du far upside skew risk metric 045 xde
shOpnPos	INT		0	shares startofday
fcOpnPos	INT		0	futures contracts startofday
cnOpnPos	INT		0	options contracts startofday
cnAtmEquiv	FLOAT		0	atm equivalent contracts oprcnOpnPos oprcnBot oprcnSld oprve opratmVega
shBot	INT		0	shares bot
shSld	INT		0	shares sld
shSldShrt	INT		0	shares sld short
fcBot	INT		0	future contracts bot
fcSld	INT		0	future contracts sld
cnBot	INT		0	option contracts bot
cnSld	INT		0	option contracts sld
cnOpened	INT		0	option contracts opened
cnClosed	INT		0	option contracts closed
opnDir	VARCHAR(2)		''	open startofday direction LN or SH
posDir	VARCHAR(2)		''	current startofday trades direction LN or SH
unitOpnPos	INT		0	effective opening position shares or cn pointValue startofday
unitCurPos	INT		0	effective current position shares or cn pointValue startofday trades
unitQtyBot	INT		0	effective shares bot shares or cn underlierPerCn
unitQtySld	INT		0	effective shares sld shares or cn underlierPerCn
dayPnl	FLOAT		0	day pnl
dayDnPnl	FLOAT		0	day pnl delta neutral
dayTrdDelta	FLOAT		0	day trade delta
dayTrdDDelta	FLOAT		0	day trade delta
dayTrdGamma	FLOAT		0	day trade gamma
dayTrdDGamma	FLOAT		0	day trade gamma
dayTrdVega	FLOAT		0	day trade vega
dayTrdWVega	FLOAT		0	day trade wVega vega vol
dayTrdTVega	FLOAT		0	day trade tVega vega sqrtmax01 years 4
dayTrdWtVega	FLOAT		0	day trade wtVega vega vol sqrtmax01 years 4
dayTrdTheta	FLOAT		0	day trade theta
dayTrdDBeta	FLOAT		0	day trade beta dayTrdDDelta beta
dayTrdVolga	FLOAT		0	day trade volga
dayTrdVanna	FLOAT		0	day trade vanna
dayTrdRho	FLOAT		0	day trade rho
dayTrdPhi	FLOAT		0	day trade phi
dayEdgeOpened	FLOAT		0	day theo option edge opened requires a theo model
dayEdgeClosed	FLOAT		0	day theo option edge closed requires a theo model
dayMnyBot	DOUBLE		0	day money bot
dayMnySld	DOUBLE		0	day money sld
opnPnlVolMark	FLOAT		0	cursurface prsurface cnOpnClr upc
opnPnlMidMark	FLOAT		0	curmid prmid cnOpnClr upc
opnPnlClrMark	FLOAT		0	curmid prclr cnOpnClr upc
opnClrMark	FLOAT		0	startofday clearing mark should be corp action adjusted
opnDivPnL	FLOAT		0	dividends paidreceived since prior period
opnMarkBrkMny	FLOAT		0	cursurface curmid unitCurPos
opnTheoEdge	FLOAT		0	opening edge edge at the end of prior period requires a theo model
posTheoEdge	FLOAT		0	current edge opening positions trades requires a theo model
tVolMove	FLOAT		0	LNtVol tVolPr
tEdgeMove	FLOAT		0	LNtVol iVol LNtVolPr iVolPr
tEdge	FLOAT		0	LNtVol iVol
opnPnlDe	FLOAT		0	open position mult dePr dUPrc BS delta PnL NOT hedgeDe
opnPnlGa	FLOAT		0	open position mult 05 avggammagammaPr dUPrc dUPrc
opnPnlTh	FLOAT		0	open position mult decomposedThetadYearsV dYearsC
opnPnlVe	FLOAT		0	open position mult optVega dVol includes spotinduced vol change
opnPnlVo	FLOAT		0	open position mult 05 avgvolgavolgaPr dVol dVol
opnPnlVa	FLOAT		0	open position mult optVanna dVol dUPrc
opnPnlSl	FLOAT		0	open position mult vePr veSlopePr dUPrc hedge effect overlaps with opnPnlVe
opnPnlRate	FLOAT		0	open position mult optRho dRate
opnPnlSDiv	FLOAT		0	open position mult optPhi dSDiv
opnPnlDDiv	FLOAT		0	open position mult dePr dDDiv uses BS delta not phi
opnPnlErr	FLOAT		0	unattributed pnl opnPnlSv opnPnlGa opnPnlTh opnPnlVe opnPnlVo opnPnlVa opnPnlRate opnPnlSDiv opnPnlDDiv
opnEdgeChange	FLOAT		0	open position mult tEdge tEdgePr requires a theo model
posDelta	FLOAT		0	live position mult optDelta
posDDelta	FLOAT		0	live position mult optDelta UPrc
posDBeta	FLOAT		0	live position mult optDelta beta UPrc
posGamma	FLOAT		0	live position mult optGamma
posDGamma	FLOAT		0	live position mult optGamma uPrc uPrc 100
posVega	FLOAT		0	live position mult optVega
posWVega	FLOAT		0	live position mult optVega iVol
posTVega	FLOAT		0	live position mult optVega sqrtmax01 years 4
posWtVega	FLOAT		0	live position mult optVega iVol sqrtmax01 years 4
posVolga	FLOAT		0	live position mult optVolg
posVanna	FLOAT		0	live position mult optVanna
posTheta	FLOAT		0	live position mult optTheta
posRho	FLOAT		0	live position mult optRho
posPhi	FLOAT		0	live position mult optPhi
opnDelta	FLOAT		0	open startofday position mult optDelta live greeks
opnDDelta	FLOAT		0	open position mult optDelta UPrc live greeks
opnDBeta	FLOAT		0	open position mult optDelta beta UPrc live greeks
opnGamma	FLOAT		0	open position mult optGamma live greeks
opnDGamma	FLOAT		0	open position mult optGamma uPrc uPrc 100 live greeks
opnVega	FLOAT		0	open position mult optVega live greeks
opnWVega	FLOAT		0	open position mult optVega iVol live greeks
opnTVega	FLOAT		0	open position mult optVega sqrtmax01 years 4 live greeks
opnWtVega	FLOAT		0	open position mult optVega iVol sqrtmax01 years 4 live greeks
opnVolga	FLOAT		0	open position mult optVolga live greeks
opnVanna	FLOAT		0	open position mult optVanna live greeks
opnTheta	FLOAT		0	open position mult optTheta live greeks
opnRho	FLOAT		0	open position mult optRho live greeks
opnPhi	FLOAT		0	open position mult optPhi live greeks
posHDelta	FLOAT		0	hedge delta either ivol or tvol basedfollows binary rules
posHDDelta	FLOAT		0	hedge delta dollarized
posHGamma	FLOAT		0	hedge gamma either ivol or tvol basedfollows binary rules
posHDGamma	FLOAT		0	hedge gamma dollarized
posNotional	FLOAT		0	live position mult uPrc
posMktValue	FLOAT		0	live position mult SR surface mark
posPremOPar	FLOAT		0	live position mult premium over parity
atmVol	FLOAT		0	atm volatility
symVol	FLOAT		0	underlier symbol volatility term21dcensored
srSlope	FLOAT		0	strike veSlope sr surfaceoptions only
prcSVol	FLOAT		0	SR Surface Vol
prcTOpx	FLOAT		0	SR Surface Price
prcYOpx	FLOAT		0	SR Yesterday Surface Price
prcUPrc	FLOAT		0	underlier price
prcYears	FLOAT		0	yearstoexpiry
prcRate	FLOAT		0	discount ratetoexpiry
prcSdiv	FLOAT		0	continuous stock div ratetoexpiry
prcDdiv	FLOAT		0	cumulative discrete dividend paid through expiry
uPrcRatio	FLOAT		0	stock price multiplier
uPrcMove	FLOAT		0	Underlier price change
iVolMove	FLOAT		0	Implied vol change fixed strike ivol change from prior period
iVolDiff	FLOAT		0	todays ivol previous ivol
exDivAmt	FLOAT		0	estimated exDiv amount if any
borrowRate	FLOAT		0	estimated stock borrow rate if any
curMidMark	DOUBLE		0	current mid market price
modelType	TINYINT UNSIGNED		0	SRPricingLibCalcModelType
underliersPerCn	DOUBLE		0	underliers per contract
underlierType	enum - UnderlierType		'None'	underlier type used for option pricing
pointValue	FLOAT		0	value of one point
pointCurrency	enum - Currency		'None'
tickValue	FLOAT		0	value of one tick
multihedge	enum - Multihedge		'None'
multihedgeSource_at	enum - AssetType		'None'	MH Ticker Key starts with if exists
multihedgeSource_ts	enum - TickerSrc		'None'	MH Ticker Key starts with if exists
multihedgeSource_tk	VARCHAR(12)		''	MH Ticker Key starts with if exists
multihedgePVRatio	FLOAT		0	pv multiplier fraction of underlier value uPerCn x uPrc SUMuPerCn x uPrc associated with this fragment
spanPrcIncUp	FLOAT		0	default exchange span price up increment for this asset
spanPrcIncDn	FLOAT		0	default exchange span price dn increment for this asset
spanVolInc	FLOAT		0	default exchange span volatility increment for this asset
prcSpanType	enum - SpanType		'None'	exchange prcSpan type
volSpanType	enum - SpanType		'None'	exchange volSpan type
spanPricingModel	enum - SpanPricingModel		'None'	Exchangedefined model for SPAN option pricing calcs
marginType	enum - MarginType		'None'	margin slide type NMSEquity 15 NMSIndex 86 NMSMedium 10
Kstar	FLOAT		0	strike that solves BSKstar surfVol BSAMERK surfVol
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
symVol63d	FLOAT		0	underlier symbol volatility term63dcensored 1day move pin for posDGamma1d
posDGamma1d	FLOAT		0	expected delta change for a 1sigma 1day underlier move posDGamma symVol63d 100 sqrt252
marginUDnVDn	FLOAT		0	position margin slide corner uPrc dn vol dn additive margin min0 corner sums
marginUDnVUp	FLOAT		0	position margin slide corner uPrc dn vol up
marginUUpVDn	FLOAT		0	position margin slide corner uPrc up vol dn
marginUUpVUp	FLOAT		0	position margin slide corner uPrc up vol up
opnMarginUDnVDn	FLOAT		0	startofday position margin slide corner live unit corners uPrc dn vol dn
opnMarginUDnVUp	FLOAT		0	startofday position margin slide corner uPrc dn vol up
opnMarginUUpVDn	FLOAT		0	startofday position margin slide corner uPrc up vol dn
opnMarginUUpVUp	FLOAT		0	startofday position margin slide corner uPrc up vol up

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
secKey_tk	2
secKey_yr	3
secKey_mn	4
secKey_dy	5
secKey_xx	6
secKey_cp	7
secKey_at	8
secKey_ts	9
secType	10
tradeDate	11
riskView	12
clientFirm	13

SELECT TABLE EXAMPLE QUERY

SELECT *
FROM `SRRisk`.`MsgRiskCubeDetailV6`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY','RATE','CRDT','GCR','SWAP','ENT','MBS','SET','LOAN') */ 
    `secKey_at` = 'NONE'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','SCE','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFEC','ICEFEF','CEQT','TSX','TMX','CRCY','CMDY','DTI','SOVN','SR_US','SR_EU','SR_AP','SR_UK','USD','EUR','GBP','CHF','JPY','CAD','AUD','HKD','KRW','INR','SGD','TWD','CNY','CNH','BRL','MXN','ZAR','SEK','NOK','DKK','PLN','ILS','TRY','NZD','THB','MYR','IDR','PHP','CZK','HUF','SAR','AED','CLP','COP') */ 
    `secKey_ts` = 'NONE'
  AND
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk` = 'Example_secKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `secKey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp` = 'CALL'
  AND
    /* Replace with a ENUM('None','Call','Put','Stock','Future','Cash') */ 
    `secType` = 'NONE'
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01'
  AND
    /* Replace with a ENUM('None','Live','Close') */
    `riskView` = 'NONE'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRRisk.doccolumns WHERE TABLE_NAME='RiskCubeDetailV6' ORDER BY ordinal_position ASC;